/*java jdk 1.7+
# -*- coding: utf-8 -
'''
Created on 2020年05月10日
@author: zxyong 13738196011
'''*/

package com.zxy.db_Self;

import com.zxy.common.Com_Fun;
import com.zxy.common.Com_Para;
import java.net.URLDecoder;
import java.sql.Array;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Date;

public class PooledConnection_Self
{
	public long					updtime		= 0;
	public boolean				b_nocursor	= true;
	public CallableStatement	calls		= null;
	public Connection			connection	= null;
	private boolean				busy;
	public int					lastrowid	= -1;
	public int 					rowsCount	= 1;

	public PooledConnection_Self(Connection connection)
	{
		this.b_nocursor = true;
		this.connection = connection;
		this.updtime = new java.util.Date().getTime();
	}

	public void BeginTranSaction() throws SQLException
	{
		this.connection.setAutoCommit(false);
	}

	public void CommitTranSaction() throws SQLException
	{
		this.connection.commit();
		this.connection.setAutoCommit(true);
	}

	public void RollBackTranSaction() throws SQLException
	{
		this.connection.rollback();
	}

	public ResultSet executeQuery(StringBuilder sql) throws SQLException
	{
		ResultSet rst = null;
		this.updtime = new java.util.Date().getTime();		
		Statement st = this.connection.createStatement();
		rst = st.executeQuery(sql.toString());
		return rst;
	}

	public int executeUpdate(StringBuilder sql) throws SQLException
	{
		this.updtime = new java.util.Date().getTime();
		Statement st = this.connection.createStatement();
		int iResult = st.executeUpdate(sql.toString());
		// this.CommitTranSaction();
		return iResult;
	}

	public ResultSet executeProcedure(String ProName, String[] Parameters, String[] ParamTypes, String[] ParamOutName, String[] ParamOutType) throws Exception
	{
		this.updtime = new java.util.Date().getTime();
		ResultSet rst = null;
		if((Parameters.length == ParamTypes.length)
				&& (ParamOutName.length == ParamOutType.length))
		{
			StringBuilder strSql = new StringBuilder();
			strSql.append("{call " + ProName + "(");
			for(int i = 0;i < Parameters.length + ParamOutName.length;i++)
			{
				if(i != 0)
					strSql.append(",?");
				else
					strSql.append("?");
			}
			strSql.append(")}");
			this.calls = this.connection.prepareCall(strSql.toString());
			for(int i = 0;i < Parameters.length;i++)
			{
				if(ParamTypes[i].toUpperCase().equals("STRING"))
				{
					this.calls.setString(i + 1,Parameters[i]);
					// this.calls.setString(i
					// + 1,Com_Fun.s_decode(Parameters[i],Com_Para.U_CODE));
				}else if(ParamTypes[i].toUpperCase().equals("INT"))
				{
					this.calls.setInt(i + 1,Integer.parseInt(Parameters[i]));
				}else if(ParamTypes[i].toUpperCase().equals("FLOAT"))
				{
					this.calls.setFloat(i + 1,Float.parseFloat(Parameters[i]));
				}else if(ParamTypes[i].toUpperCase().equals("DATE"))
				{
					String temDate = Com_Fun.s_decode(Parameters[i],Com_Para.U_CODE);
					calls.setTimestamp(i
							+ 1,new java.sql.Timestamp(Com_Fun.StrDateTime("yyyy-MM-dd HH:mm:ss",temDate).getTime()));
				}else if(ParamTypes[i].toUpperCase().equals("LIST"))
				{
					Array ary_t = connection.createArrayOf("VARCHAR",Parameters[i].split(","));
					calls.setArray(i + 1,ary_t);
				}
			}
			int i_Cursor = -1;
			for(int i = 0;i < ParamOutName.length;i++)
			{
				if(ParamOutType[i].toUpperCase().equals("STRING"))
				{
					this.calls.registerOutParameter(i + 1
							+ Parameters.length,12);
				}else if(ParamOutType[i].toUpperCase().equals("INT"))
				{
					this.calls.registerOutParameter(i + 1
							+ Parameters.length,4);
				}else if(ParamOutType[i].toUpperCase().equals("FLOAT"))
				{
					this.calls.registerOutParameter(i + 1
							+ Parameters.length,6);
				}else if(ParamOutType[i].toUpperCase().equals("DATE"))
				{
					this.calls.registerOutParameter(i + 1
							+ Parameters.length,91);
				}else if(ParamOutType[i].toUpperCase().equals("CURSOR"))
				{
					this.calls.registerOutParameter(i + 1
							+ Parameters.length,-10);
					this.b_nocursor = false;
					i_Cursor = i + 1 + Parameters.length;
				}
			}

			if(ParamOutName.length > 0)
			{
				this.calls.execute();
				if(i_Cursor > 0)
				{
					rst = (ResultSet) this.calls.getObject(i_Cursor);
				}else
				{
					strSql.delete(0,strSql.length());
					strSql.append("select ");
					for(int i = 0;i < ParamOutName.length;i++)
					{
						if(i != 0)
							strSql.append(",");
						if(ParamOutType[i].toUpperCase().equals("STRING"))
						{
							strSql.append("'"
									+ this.calls.getString(i + 1
											+ Parameters.length)
									+ "' as '" + ParamOutName[i] + "'");
						}else if(ParamOutType[i].toUpperCase().equals("INT"))
						{
							strSql.append("'"
									+ this.calls.getInt(i + 1
											+ Parameters.length)
									+ "' as '" + ParamOutName[i] + "'");
						}else if(ParamOutType[i].toUpperCase().equals("FLOAT"))
						{
							strSql.append("'"
									+ this.calls.getFloat(i + 1
											+ Parameters.length)
									+ "' as '" + ParamOutName[i] + "'");
						}else if(ParamOutType[i].toUpperCase().equals("DATE"))
						{
							strSql.append("'"
									+ new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(this.calls.getDate(i
											+ 1 + Parameters.length))
									+ "' as '" + ParamOutName[i] + "'");
						}
					}
					rst = this.connection.createStatement().executeQuery(strSql.toString());
					calls.close();
				}
			}else
			{
				rst = this.calls.executeQuery();
			}
		}
		return rst;
	}

	public ResultSet ParamExecuteQuery(String ProName, String[] Parameters, String[] ParamTypes, String[] ParamOutName, String[] ParamOutType, StringBuilder strSql) throws Exception
	{
		this.updtime = new Date().getTime();
		ResultSet rst = null;
		if(Parameters.length == ParamTypes.length
				&& ParamOutName.length == ParamOutType.length)
		{

			for(int ks = 0;ks < ParamTypes.length;ks++)
			{
				if(ParamTypes[ks].equals("LIST"))
				{
					String strtemSql = strSql.toString();
					String str_v = "";
					for(int j = 0;j < Parameters[ks].split(",").length;j++)
					{
						if(j != 0)
							str_v += ",";
						str_v += "?";
					}
					strSql.delete(0,strSql.length());
					strSql.append(strtemSql.replaceFirst("@\\?",str_v));
				}
			}
			int ListCount = 0;

			PreparedStatement psmt = connection.prepareStatement(strSql.toString(),Statement.RETURN_GENERATED_KEYS);
			for(int i = 0;i < Parameters.length;i++)
			{
				if(ParamTypes[i].toUpperCase().equals("STRING"))
				{
					// psmt.setObject(i + ListCount
					// + 1,(Object)
					// Com_Fun.s_decode(Parameters[i],Com_Para.U_CODE));
					psmt.setObject(i + ListCount + 1,Parameters[i]);
				}else if(ParamTypes[i].toUpperCase().equals("LIKESTRING"))
				{
					psmt.setObject(i + ListCount + 1,"%" + Parameters[i] + "%");
					// psmt.setObject(i + ListCount + 1,"%"
					// + (Object)
					// Com_Fun.s_decode(Parameters[i],Com_Para.U_CODE)
					// + "%");
				}else if(ParamTypes[i].toUpperCase().equals("INT"))
				{
					psmt.setInt(i + ListCount
							+ 1,Integer.parseInt(Parameters[i]));
				}else if(ParamTypes[i].toUpperCase().equals("FLOAT"))
				{
					psmt.setFloat(i + ListCount
							+ 1,Float.parseFloat(Parameters[i]));
				}else if(ParamTypes[i].toUpperCase().equals("DATE"))
				{
					String temDate = Com_Fun.s_decode(Parameters[i],Com_Para.U_CODE);
					psmt.setTimestamp(i + ListCount
							+ 1,new java.sql.Timestamp(Com_Fun.StrDateTime("yyyy-MM-dd HH:mm:ss",temDate).getTime()));
				}else if(ParamTypes[i].toUpperCase().equals("LIST")
						&& connection.toString().indexOf("sqlite") != -1)
				{
					Array ary_t = connection.createArrayOf("VARCHAR",Parameters[i].split(","));
					// Array ary_t =
					// connection.createArrayOf("VARCHAR",Com_Fun.s_decode(Parameters[i],Com_Para.U_CODE).split(","));
					psmt.setArray(i + ListCount + 1,ary_t);
				}else if(ParamTypes[i].toUpperCase().equals("LIST"))
				{
					String[] strV = Parameters[i].split(",");
					for(int k = 0;k < strV.length;k++)
					{
						psmt.setString(i + ListCount + 1 + k,strV[k]);
						// psmt.setString(i + ListCount + 1
						// + k,Com_Fun.s_decode(strV[k],Com_Para.U_CODE));
					}
					ListCount += strV.length - 1;
				}
			}
			if(strSql.toString().toUpperCase().trim().indexOf("INSERT INTO") == 0
					|| strSql.toString().toUpperCase().trim().indexOf("UPDATE") == 0)
			{
				int iRes = psmt.executeUpdate();
				ResultSet keys = psmt.getGeneratedKeys();
				if(keys.next())
					this.lastrowid = keys.getInt(1);
				psmt.close();
				if(connection.getClass().toString().indexOf("oracle") != -1)
				{
					if(iRes > 0)
						rst = connection.createStatement().executeQuery("select '1' s_result,'成功' error_desc from dual");
					else
						rst = connection.createStatement().executeQuery("select '0' s_result,'失败' error_desc from dual");
				}else
				{
					if(iRes > 0)
						rst = connection.createStatement().executeQuery("select '1' as 's_result','成功' as 'error_desc';");
					else
						rst = connection.createStatement().executeQuery("select '0' as 's_result','失败' as 'error_desc';");
				}
			}else if(strSql.toString().toUpperCase().trim().indexOf("DELETE") == 0)
			{
				if(strSql.toString().indexOf(";") != -1)
				{
					String[] strSqls = strSql.toString().split(";");
					Statement st = connection.createStatement();
					for(String s1: strSqls)
					{
						st.addBatch(s1);
					}
					// 执行批处理sql
					st.executeBatch();
					st.clearBatch();
					st.close();
					if(connection.getClass().toString().indexOf("oracle") != -1)
					{
						rst = connection.createStatement().executeQuery("select '1' s_result,'成功' error_desc from dual");
					}else
						rst = connection.createStatement().executeQuery("select '1' as 's_result','成功' as 'error_desc';");
				}else
				{
					int bRes = psmt.executeUpdate();
					psmt.close();
					if(connection.getClass().toString().indexOf("oracle") != -1)
					{
						if(bRes != 0)
							rst = connection.createStatement().executeQuery("select '1' s_result,'成功' error_desc from dual");
						else
							rst = connection.createStatement().executeQuery("select '0' s_result,'失败' error_desc from dual");
					}else
					{
						if(bRes != 0)
							rst = connection.createStatement().executeQuery("select '1' as 's_result','成功' as 'error_desc';");
						else
							rst = connection.createStatement().executeQuery("select '0' as 's_result','失败' as 'error_desc';");
					}
				}
			}else
			{
				rst = psmt.executeQuery();
			}
		}
		return rst;
	}

	public Connection getConnection()
	{
		return this.connection;
	}

	public void setConnection(Connection connection)
	{
		this.connection = connection;
	}

	public boolean isBusy()
	{
		return this.busy;
	}

	public void setBusy(boolean busy)
	{
		this.busy = busy;
	}

	public void close()
	{
		this.busy = false;
	}

	public void returnConnection()
	{
		DBManager_Self.returnConnection(this.connection);
	}

	/**
	 * @return updtime
	 */
	public long getUpdtime()
	{
		return updtime;
	}

	/**
	 * @param updtime 要设置的 updtime
	 */
	public void setUpdtime(long updtime)
	{
		this.updtime = updtime;
	}

	/**
	 * @return b_nocursor
	 */
	public boolean isB_nocursor()
	{
		return b_nocursor;
	}

	/**
	 * @param b_nocursor 要设置的 b_nocursor
	 */
	public void setB_nocursor(boolean b_nocursor)
	{
		this.b_nocursor = b_nocursor;
	}

	/**
	 * @return calls
	 */
	public CallableStatement getCalls()
	{
		return calls;
	}

	/**
	 * @param calls 要设置的 calls
	 */
	public void setCalls(CallableStatement calls)
	{
		this.calls = calls;
	}

	/**
	 * @return lastrowid
	 */
	public int getLastrowid()
	{
		return lastrowid;
	}

	/**
	 * @param lastrowid 要设置的 lastrowid
	 */
	public void setLastrowid(int lastrowid)
	{
		this.lastrowid = lastrowid;
	}

	/**
	 * @return rowsCount
	 */
	public int getRowsCount()
	{
		return rowsCount;
	}

	/**
	 * @param rowsCount 要设置的 rowsCount
	 */
	public void setRowsCount(int rowsCount)
	{
		this.rowsCount = rowsCount;
	}
}